package com.demo2do.fms.scheduler.dao.fms;

import java.util.Date;
import java.util.List;

import com.demo2do.core.persistence.annotation.Dao;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Update;
import com.demo2do.core.persistence.annotation.Variable;
import com.demo2do.fms.entity.driver.Driver;
import com.demo2do.fms.entity.vehicle.source.Vehicle;
import com.demo2do.fms.entity.vehicle.source.VehicleGpsInfo;
import com.demo2do.fms.entity.vehicle.tractor.Tractor;
import com.demo2do.fms.entity.vehicle.trailer.Trailer;
import com.demo2do.fms.scheduler.entity.RemoteTransMeans;
import com.demo2do.fms.scheduler.entity.VehicleSource;

/**
 *
 * @author Ark
 *
 */
@Dao
public interface VehicleLockDao {
	
	// ***************  lock by no driver ********************
	
	/**
	 * 查询绑定合格司机的车辆
	 * 
	 * @return
	 */
	@Query("SELECT v.id, v.vehicle_code AS code FROM VW_VEHICLE v WHERE v.is_disabled = 0 AND NOT EXISTS (SELECT 1 FROM FMS_DRIVER d WHERE v.id = d.vehicle_id AND d.is_disabled = 0 AND d.status = 'QUALIFIED')")
	public List<Vehicle> listNoDriverVehicle();
	
	/**
	 * load vehicleSource by code
	 * 
	 * @param code
	 * @return
	 */
	@Query("SELECT id, vehicle_code AS code, is_lock AS locking, status, is_synchronize AS synchronize FROM FMS_VEHICLE_SOURCE WHERE vehicle_code = :code AND is_disabled = 0")
	public VehicleSource loadVehicleSource(@Variable("code") String code);
	
	/**
	 * load deleted VehicleSource by code
	 * 
	 * @param code
	 * @return
	 */
	@Query("SELECT id, vehicle_code AS code, is_lock AS locking, status, is_synchronize AS synchronize FROM FMS_VEHICLE_SOURCE WHERE vehicle_code = :code AND is_disabled = 1 ORDER BY LAST_MODIFY_TIME DESC")
    public VehicleSource loadDeletedVehicleSource(@Variable("code") String code);
	
	/**
	 * load remoteTransMeans by code
	 * 
	 * @param code
	 * @return
	 */
	@Query("SELECT id, code, lock_type, is_special_trans AS special_trans, is_locked AS locking FROM REMOTE_TRANS_MEANS WHERE code = :code AND is_disabled = 0")
	public RemoteTransMeans loadRemoteTransMeans(@Variable("code") String code);
	
	/**
	 * lock vehicleSource
	 * 
	 * @param vehicleSource
	 */
	@Update("UPDATE FMS_VEHICLE_SOURCE SET is_lock = :locking, lock_reason = :lockReason, lock_type = :lockType, last_modify_time = :lastModifyTime WHERE id = :id")
	public void updateVehicleSource(VehicleSource vehicleSource);
	
	/**
	 * lock remoteTransMeans
	 * 
	 * @param remoteTransMeans
	 */
	@Update("UPDATE REMOTE_TRANS_MEANS SET is_locked = :locking, lock_type = :lockType, lock_unlock_operator = :lockUnlockOperator, unlock_date = :unlockDate, last_operator = :lastOperator, last_modified = :lastModified WHERE id = :id")
	public void updateRemoteTransMeans(RemoteTransMeans remoteTransMeans);
	
	
	// *************** lock by no gps *****************
	
	/**
	 * list in 7 day, have no gps info 
	 * 
	 * @param lastModified
	 * @return
	 */
	@Query("SELECT vid FROM VW_TMS_VEHICLE_INFO WHERE last_modified < :lastModified")
	public List<VehicleGpsInfo> listNeedLockVehicle(@Variable("lastModified") Date lastModified);
	
	/**
	 * 没有gps信息的车辆
	 * 
	 * @return
	 */
	@Query("SELECT means.id AS vid FROM REMOTE_TRANS_MEANS means LEFT JOIN VW_TMS_VEHICLE_INFO info ON means.id = info.vid WHERE info.vid IS NULL AND means.is_disabled = 0")
	public List<VehicleGpsInfo> listNoGpsVehicle();
	
	/**
	 * load remoteTransMeans by id
	 * 
	 * @param id
	 * @return
	 */
	@Query("SELECT id, code, lock_type, is_special_trans AS special_trans, is_locked AS locking FROM REMOTE_TRANS_MEANS WHERE id = :id AND is_disabled = 0")
	public RemoteTransMeans loadRemoteTransMeans(@Variable("id") Long id);
	
	
	// *************** unlock by gps *****************
	
	/**
	 * list vehicleId have gps in lastModified
	 * 
	 * @param lastModified
	 * @return
	 */
	@Query("SELECT vid FROM VW_TMS_VEHICLE_INFO WHERE last_modified > :lastModified")
	public List<VehicleGpsInfo> listNeedUnlockVehicle(@Variable("lastModified") Date lastModified);
	
	/**
	 * load vehicle by code
	 * 
	 * @param code
	 * @return
	 */
	@Query("SELECT id FROM VW_VEHICLE WHERE vehicle_code = :code AND is_disabled = 0")
	public Vehicle loadVehicle(@Variable("code") String code);

	/**
	 * list driver by vehicleId and status
	 * 
	 * @param vehicleId
	 * @param status
	 * @return
	 */
	@Query("SELECT id FROM FMS_DRIVER WHERE vehicle_id = :vehicleId AND status = :status AND is_disabled = 0")
	public List<Driver> listDriver(@Variable("vehicleId") Long vehicleId, @Variable("status") String status);
	
	
	
	// *************** sync mdm vehicle status *****************
	
	/**
	 * list remoteTransMeans by unlockDate 
	 * 
	 * @param unlockDate
	 * @return
	 */
	@Query("SELECT id, code, lock_type, is_special_trans AS special_trans, is_locked AS locking, mark FROM REMOTE_TRANS_MEANS WHERE unlock_date > :unlockDate AND is_disabled = 0")
	public List<RemoteTransMeans> listRemoteTransMeans(@Variable("unlockDate") Date unlockDate);

	/**
	* @description
	* @params []
	* @return java.util.List<com.demo2do.fms.entity.vehicle.source.VehicleSource>
	* @author LZJ
	* @date 2020/12/1 17:09
	**/
	@Query("SELECT id, vehicle_code AS code, is_lock AS locking, status, is_synchronize AS synchronize FROM FMS_VEHICLE_SOURCE WHERE is_disabled = 0 AND is_lock = 1")
	List<VehicleSource> listLockVehicle();

	/**
	* @description
	* @params []
	* @return java.util.List<com.demo2do.fms.scheduler.entity.VehicleSource>
	* @author LZJ
	* @date 2020/12/2 15:15
	**/
	@Query("FROM FMS_VEHICLE_SOURCE WHERE is_disabled = 0 AND lock = 0")
    List<com.demo2do.fms.entity.vehicle.source.VehicleSource> listUnlockVehicle();

	/**
	* @description
	* @params [tractorBrandCode]
	* @return com.demo2do.fms.entity.vehicle.tractor.Tractor
	* @author LZJ
	* @date 2020/12/2 15:24
	**/
	@Query("FROM FMS_TRACTOR WHERE brandCNCode =:tractorBrandCode AND is_disabled = 0")
	Tractor loadTractor(@Variable("tractorBrandCode") String tractorBrandCode);

	/**
	* @description
	* @params [trailerBrandCode]
	* @return com.demo2do.fms.entity.vehicle.trailer.Trailer
	* @author LZJ
	* @date 2020/12/2 15:28
	**/
	@Query("FROM FMS_TRAILER brandCNCode =:trailerBrandCode AND is_disabled = 0")
	Trailer loadTrailer(@Variable("trailerBrandCode") String trailerBrandCode);
}
